Mondial Database
Schema definitions
Table of contents | | Index |
TypesTables | | |
CREATE OR REPLACE TYPE GeoCoord AS OBJECT (
Latitude NUMBER,
Longitude NUMBER
);
/
CREATE TABLE Country (
Name VARCHAR2(40)
CONSTRAINT Country_Name_NotNull NOT NULL
CONSTRAINT Country_Name_Unique UNIQUE,
Code CHAR(2)
CONSTRAINT Country_Key PRIMARY KEY,
Capital VARCHAR2(40),
Province VARCHAR2(40),
Population NUMBER
CONSTRAINT Country_Population_Check CHECK (
Population >= 0
),
Area NUMBER
CONSTRAINT Country_Area_Check CHECK (
Area >= 0
)
);
CREATE TABLE Province (
Name VARCHAR2(40),
Country CHAR(2),
Population NUMBER
CONSTRAINT Province_Population_Check CHECK (
Population >= 0
),
Area NUMBER
CONSTRAINT Province_Area_Check CHECK (
Area >= 0
),
Capital VARCHAR2(40),
CapProv VARCHAR2(40),
CONSTRAINT Province_Key PRIMARY KEY (Country, Name)
);
CREATE TABLE City (
Name VARCHAR2(40),
Country CHAR(2),
Province VARCHAR2(40),
Population NUMBER
CONSTRAINT City_Population_Check CHECK (
Population >= 0
),
Latitude NUMBER
CONSTRAINT City_Latitude_Check CHECK (
(Latitude >= -90) AND (Latitude <= 90)
),
Longitude NUMBER
CONSTRAINT City_Longitude_Check CHECK (
(Longitude >= -180) AND (Longitude <= 180)
),
CONSTRAINT City_Key PRIMARY KEY (Country, Province, Name)
);
CREATE TABLE Continent (
Name VARCHAR2(20)
CONSTRAINT Continent_Key PRIMARY KEY,
Area NUMBER
CONSTRAINT Continent_Area_Check CHECK (
Area >= 0
)
);
CREATE TABLE encompasses (
Country CHAR(2),
Continent VARCHAR2(20),
Percentage NUMBER
CONSTRAINT encompasses_Percentage_Check CHECK (
(Percentage > 0) AND (Percentage <= 100)
),
CONSTRAINT encompasses_Key PRIMARY KEY (Continent, Country)
);
CREATE TABLE borders (
Country1 CHAR(2),
Country2 CHAR(2),
Length NUMBER
CONSTRAINT borders_Length_Check CHECK (
Length > 0
),
CONSTRAINT borders_Key PRIMARY KEY (Country1, Country2)
);
CREATE TABLE Organization (
Abbreviation VARCHAR2(15)
CONSTRAINT Organization_Key PRIMARY KEY,
Name VARCHAR2(100)
CONSTRAINT Organization_Name_NotNull NOT NULL
CONSTRAINT Organization_Name_Unique UNIQUE,
Established DATE,
City VARCHAR2(40),
Province VARCHAR2(40),
Country CHAR(2)
);
CREATE TABLE is_member (
Organization VARCHAR2(15),
Country CHAR(2),
Type VARCHAR2(30),
CONSTRAINT is_member_Key PRIMARY KEY (Country, Organization)
);
CREATE TABLE Economy (
Country CHAR(2)
CONSTRAINT Economy_Key PRIMARY KEY,
GDP NUMBER
CONSTRAINT Economy_GDP_Check CHECK (
GDP >= 0
),
Agriculture NUMBER,
Industry NUMBER,
Services NUMBER,
Inflation NUMBER
);
CREATE TABLE Population (
Country CHAR(2)
CONSTRAINT Population_Key PRIMARY KEY,
Population_Growth NUMBER,
Infant_Mortality NUMBER
);
CREATE TABLE Politics (
Country CHAR(2)
CONSTRAINT Politics_Key PRIMARY KEY,
Independence DATE,
Government VARCHAR2(120)
);
CREATE TABLE Language (
Country CHAR(2),
Name VARCHAR2(50),
Percentage NUMBER
CONSTRAINT Language_Percentage_Check CHECK (
(Percentage > 0) AND (Percentage <= 100)
),
CONSTRAINT Language_Key PRIMARY KEY (Country, Name)
);
CREATE TABLE Religion (
Country CHAR(2),
Name VARCHAR2(50),
Percentage NUMBER
CONSTRAINT Religion_Percentage_Check CHECK (
(Percentage > 0) AND (Percentage <= 100)
),
CONSTRAINT Religion_Key PRIMARY KEY (Country, Name)
);
CREATE TABLE Ethnic_Group (
Country CHAR(2),
Name VARCHAR2(50),
Percentage NUMBER
CONSTRAINT Ethnic_Group_Percentage_Check CHECK (
(Percentage > 0) AND (Percentage <= 100)
),
CONSTRAINT Ethnic_Group_Key PRIMARY KEY (Country, Name)
);
CREATE TABLE located (
City VARCHAR2(40)
CONSTRAINT located_City_NotNull NOT NULL,
Province VARCHAR2(40)
CONSTRAINT located_Province_NotNull NOT NULL,
Country CHAR(2)
CONSTRAINT located_Country_NotNull NOT NULL,
River VARCHAR2(30),
Lake VARCHAR2(30),
Sea VARCHAR2(30)
);
CREATE TABLE River (
Name VARCHAR2(30)
CONSTRAINT River_Key PRIMARY KEY,
River VARCHAR2(30),
Lake VARCHAR2(30),
Sea VARCHAR2(30),
Length NUMBER
CONSTRAINT River_Length_Check CHECK (
Length >= 0
)
);
CREATE TABLE Mountain (
Name VARCHAR2(30)
CONSTRAINT Mountain_Key PRIMARY KEY,
Height NUMBER
CONSTRAINT Mountain_Height_Check CHECK (
Height >= 0
),
Coordinates GeoCoord
CONSTRAINT Mountain_Coordinates_Check CHECK (
(Coordinates.Longitude >= -180) AND
(Coordinates.Longitude <= 180) AND
(Coordinates.Latitude >= -90) AND
(Coordinates.Latitude <= 90)
)
);
CREATE TABLE Lake (
Name VARCHAR2(30)
CONSTRAINT Lake_Key PRIMARY KEY,
Area NUMBER
CONSTRAINT Lake_Area_Check CHECK (
Area >= 0
)
);
CREATE TABLE Sea (
Name VARCHAR2(30)
CONSTRAINT Sea_Key PRIMARY KEY,
Depth NUMBER
CONSTRAINT Sea_Depth_Check CHECK (
Depth >= 0
)
);
CREATE TABLE Island (
Name VARCHAR2(30)
CONSTRAINT Island_Key PRIMARY KEY,
Islands VARCHAR2(30),
Area NUMBER
CONSTRAINT Island_Area_Check CHECK (
Area >= 0
),
Coordinates GeoCoord
CONSTRAINT Island_Coordinates_Check CHECK (
(Coordinates.Longitude >= -180) AND
(Coordinates.Longitude <= 180) AND
(Coordinates.Latitude >= -90) AND
(Coordinates.Latitude <= 90)
)
);
CREATE TABLE Desert (
Name VARCHAR2(30)
CONSTRAINT Desert_Key PRIMARY KEY,
Area NUMBER
CONSTRAINT Desert_Area_Check CHECK (
Area >= 0
)
);
CREATE TABLE geo_river (
River VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_river_Key PRIMARY KEY (Country, Province, River)
);
CREATE TABLE geo_mountain (
Mountain VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_mountain_Key PRIMARY KEY (Country, Province, Mountain)
);
CREATE TABLE geo_lake (
Lake VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_lake_Key PRIMARY KEY (Country, Province, Lake)
);
CREATE TABLE geo_sea (
Sea VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_sea_Key PRIMARY KEY (Country, Province, Sea)
);
CREATE TABLE geo_island (
Island VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_island_Key PRIMARY KEY (Country, Province, Island)
);
CREATE TABLE geo_desert (
Desert VARCHAR2(30),
Country CHAR(2),
Province VARCHAR2(40),
CONSTRAINT geo_desert_Key PRIMARY KEY (Country, Province, Desert)
);
CREATE TABLE merges_with (
Sea1 VARCHAR2(30),
Sea2 VARCHAR2(30),
CONSTRAINT merges_with_Key PRIMARY KEY (Sea1, Sea2)
);